package com.dao.impl;

import com.dao.UserDao;
import com.entity.User;
import com.util.DruidUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.List;

public class UserDaoImpl implements UserDao {
    private QueryRunner runner=new QueryRunner();

    @Override
    public User login(String username, String password) {
        Connection connection = DruidUtils.getConnection();
        User user=null;
        String sql ="select ID,user,password,address,image,phone,name from customers where user=? and password=?";
        try {
             user = runner.query(connection, sql, new BeanHandler<>(User.class),username,password);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return user;
    }

    @Override
    public int registered(String name, String pwd) {
        QueryRunner runner = new QueryRunner();
        Connection connection = DruidUtils.getConnection();
        try {
            String sql="INSERT INTO `customers`(user,password) VALUES(?,?)";
            int update = runner.update(connection, sql, name, pwd);
            return update;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }

    @Override
    public int modify(int id, String name, String address, String phone, String image) {
        QueryRunner runner = new QueryRunner();
        Connection connection = DruidUtils.getConnection();
        try {
            String sql="update bigdata.customers set bigdata.customers.name=?,bigdata.customers.address=?,bigdata.customers.phone=?,bigdata.customers.image=? where bigdata.customers.ID=?;";
            int count = runner.update(connection, sql, name,address,phone,image,id);
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }
    @Override
    public User getUser(String name, String pwd) throws SQLException {
        QueryRunner runner = new QueryRunner();
        // 1.调用DruidUtils获取连接对象
        Connection  conn = DruidUtils.getConnection();
        // 2.准备执行的sql语句
        String sql="select * from bigdata.customers where name=? and password=?";
        // 3.调用查询方法,将查询的数据封装成User对象
        User user = runner.query(conn,sql,new BeanHandler<User>(User.class),name,pwd);
        // 4.关闭连接对象
        conn.close();
        // 5.返回user
        return user;
    }

    /**
     * 修改密码
     * @param id  需要修改密码的用户编号
     * @param pwd  新的密码
     * @return  修改的数据行
     */
    @Override
    public int modifyPwd(int id, String pwd) throws SQLException {
        String sql="update  bigdata.customers set bigdata.customers.password = ? where id=?";
        Connection conn = DruidUtils.getConnection();
        int count = runner.update(conn,sql,pwd,id);
        conn.close();
        return count;
    }

    @Override
    public User selectName(String name) {
        QueryRunner runner = new QueryRunner();
        Connection connection = DruidUtils.getConnection();
        String sql = "select `user` from customers where `user` = ?";
        try {
            User count = runner.query(connection,sql,new BeanHandler<User>(User.class),name);
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public int getCartNum(int userId) {
        Connection connection = DruidUtils.getConnection();
        String sql = "select count(id) from cart where customers_id=?";
        try {
            Number date=runner.query(connection,sql,new ScalarHandler<>(),userId);
            int cartNum = date.intValue();
            return cartNum;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {

            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }

        }
        return 0;
    }

    @Override
    public User getUserByID(int userId) {
        QueryRunner runner = new QueryRunner();
        Connection connection = DruidUtils.getConnection();
        String sql = "select * from customers where customers.ID = ?";
        try {
            User user = runner.query(connection,sql,new BeanHandler<User>(User.class),userId);
            return user;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }

    @Override
    public List<User> userList() {
        QueryRunner runner = new QueryRunner();
        Connection connection = DruidUtils.getConnection();
        String sql="select ID,user,password,state,address,image,phone,name from customers";
        try {
            List<User> query = runner.query(connection, sql, new BeanListHandler<>(User.class));
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            try {
                connection.close();
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        return null;
    }
}
